package com.x.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Component;

import com.x.model.Configuration;
import com.x.model.Shipments;

@Component
public class ConfigurationDAO extends BaseDAO {
	public Map<String, Object> list(int pageIndex, int pageSize,
			List<String[]> wherePart, List<String[]> orderPart) {
		String subSql = "(select fc,shipMethod,province,city,district,max(createtime) as time from configuration group by fc,shipMethod,province,city,district)c ";
		String countSql = "select count(*) from configuration o,"
				+ subSql
				+ " where o.fc = c.fc and o.shipMethod = c.shipMethod and o.province = c.province and o.city = c.city and o.district = c.district and o.createtime = c.time";
		String whereSql = new String();
		if (!wherePart.isEmpty()) {
			for (String[] where : wherePart) {
				whereSql += " and o." + where[0] + " like '%" + where[1] + "%'";
			}
		}
		String whereCountSql = countSql + whereSql;
		String orderSql = new String();
		if (!orderPart.isEmpty()) {
			orderSql = " order by ";
			for (String[] order : orderPart) {
				orderSql += "o." + order[0] + " " + order[1] + ", ";
			}
			orderSql = orderSql.substring(0, orderSql.lastIndexOf(","));
		}
		String limitSql = " limit " + String.valueOf(pageIndex) + ","
				+ String.valueOf(pageSize);
		String selectSql = "select o.fc,o.shipMethod,o.province,o.city,o.district,o.shipments,o.tt from configuration o,"
				+ subSql
				+ " where o.fc = c.fc and o.shipMethod = c.shipMethod and o.province = c.province and o.city = c.city and o.district = c.district and o.createtime = c.time"
				+ whereSql + orderSql + limitSql;
		Session session = this.getSession();
		Object total = session.createSQLQuery(countSql).uniqueResult();
		Object recordsFiltered = session.createSQLQuery(whereCountSql)
				.uniqueResult();
		List<Object[]> configs = session.createSQLQuery(selectSql).list();
		List<Map<String, String>> resultList = new ArrayList<>();
		for (Object[] config : configs) {
			Map<String, String> one = new HashMap<>();
			one.put("fc", config[0].toString());
			one.put("shipMethod", config[1].toString());
			one.put("province", config[2].toString());
			one.put("city", config[3].toString());
			one.put("district", config[4].toString());
			one.put("shipments", config[5].toString());
			one.put("tt", config[6].toString());
			resultList.add(one);
		}
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("recordsTotal", total);
		result.put("recordsFiltered", recordsFiltered);
		result.put("data", resultList);
		return result;
	}

	public boolean notExist(String fc, String shipMethod, String province,
			String city, String district) {
		Criteria c = this.getSession().createCriteria(Configuration.class);
		c = c.add(Restrictions.eq("fc", fc));
		c = c.add(Restrictions.eq("shipMethod", shipMethod));
		c = c.add(Restrictions.eq("province", province));
		c = c.add(Restrictions.eq("city", city));
		c = c.add(Restrictions.eq("district", district));
		return c.list().isEmpty();
	}

	public void create(String fc, String shipMethod, String province,
			String city, String district, int tt, BigDecimal shipments) {
		Configuration config = new Configuration();
		config.setFc(fc);
		config.setShipMethod(shipMethod);
		config.setProvince(province);
		config.setCity(city);
		config.setDistrict(district);
		config.setTt(tt);
		config.setShipments(shipments);
		this.getSession().merge(config);
	}

	public List<String> getProvinceSuggest() {
		Criteria c = this.getSession().createCriteria(Configuration.class);
		c.setProjection(Projections.groupProperty("province"));
		return c.list();
	}

	public List<String> getCitySuggest(String city) {
		Criteria c = this.getSession().createCriteria(Configuration.class);
		c.setProjection(Projections.groupProperty("city"));
		return c.list();
	}

	public List<String> getDistrictSuggest(String district) {
		Criteria c = this.getSession().createCriteria(Configuration.class);
		c.setProjection(Projections.groupProperty("district"));
		return c.list();
	}

	 public void update(String id, String fc, String shipMethod,
	 String province, String city, String district, int tt, BigDecimal shipments) {
	 Configuration config = getConfigById(id);
	 config.setFc(fc);
	 config.setShipMethod(shipMethod);
	 config.setProvince(province);
	 config.setCity(city);
	 config.setDistrict(district);
	 config.setTt(tt);
	 config.setShipments(shipments);
	 this.getSession().merge(config);
	
	 }

	private Configuration getConfigById(String id) {
		Criteria c = this.getSession().createCriteria(Configuration.class);
		c = c.add(Restrictions.eq("id", id));
		return (Configuration) c.uniqueResult();
	}

	public void delete(String id) {
		Configuration config = getConfigById(id);
		this.getSession().delete(config);
	}

	public List<Object> loadCity(String province) {
		Criteria c = this.getSession().createCriteria(Configuration.class);
		c.setProjection(Projections.groupProperty("city"));
		c = c.add(Restrictions.eq("province", province));
		return c.list();
	}

	public void upload(List<Configuration> configs) {
		Session session = this.getSession();
		int i = 0;
		for (Configuration config : configs) {
			session.save(config);
			if (i % 1000 == 0) {
				session.flush();
				session.clear();
			}
		}
	}
}
